﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using JustMVCTest.Models;
using ReportManagement;
using System.Data.Entity;
namespace JustMVCTest.Controllers
{
    public class ReportController : PdfViewController
    {
        private PODBContext db = new PODBContext();
        

        public class Vendor_Rep
        {
            public long PONumber;
            public int Item;
            public String Descr;
            public DateTime DeliveryDate;
            public int Status;
        }

        public class Vendor_Sum
        {
            public double Open_Amt_L_Sum;
            public int Count;
            public String VendorName;
        }
        
        public class Main_Vendor_Sum
        {
            public List<Vendor_Sum> Vendor_Sum { get; set; }
            public HeadLine HeadLine { get; set; }
        }

        public class PO_History_HeadLine
        {
            public String Vendor_Name;
            public String Descr;
        }
        
        public class Main_PO_History
        {
            public List<PO_History_Res> PO_History_Res { get; set; }
            public PO_History_HeadLine HeadLine { get; set; }
        }


        public class Vendor_Res
        {
            public long PONumber;
            public int Item;
            public String Descr;
            public String DeliveryDate;
            public String Status;
        }
        
        public class Main_Vendor
        {
            public List<Vendor_Res> Vendor_Res { get; set; }
            public HeadLine HeadLine{ get; set; }
        }
        
        public class HeadLine
        {
            public int CountPos;
        }

        public class Requestio_Sum
        {
            public String Requestio;
            public int Count;
        }

        public class CC_Sum
        {
            public String CC;
            public int Count;
        }

        public class IO_Sum
        {
            public String IO;
            public int Count;
        }

        public class Main_Requestio_Rep
        {
            public List<Requestio_Res> Requestio_Res { get; set; }
            public HeadLine HeadLine { get; set; }
        }

        public class Requestio_Rep
        {
            public long PONumber;
            public int Item;
            public String Vendor_Name;
            public String Descr;
            public Double Open_Amt_L;
            public DateTime DeliveryDate;
        }

        public class Requestio_Res
        {
            public long PONumber;
            public int Item;
            public String Vendor_Name;
            public String Descr;
            public Double Open_Amt_L;
            public String DeliveryDate;
        }

        public class TopDelaying_Rep
        {
            public String Name;
            public String Email;
            public int Count;
        }

      public class PO_History_Rep
        {
            public int Item;
            public int Update_Num;
            public int Status;
            public DateTime DeliveryDate;
            public DateTime DeliveryDate_Req;
            public int Percentage;
        }
         public class PO_History_Res
        {
            public int Item;
            public int Update_Num;
            public String Status;
            public String DeliveryDate;
            public String DeliveryDate_Req;
            public String Percentage;
        }

        public class Rate
        {
            public String CostCenter;
            public long PONumber;
            public int Item;
            public int ActualPercent;
            public int PlannedPercent;
            public DateTime DeliveryDate;
            public int Delta;
            public DateTime PurchO;
        }

        public class Rate_Res
        {
            public String CostCenter;
            public long PONumber;
            public int Item;
            public String ActualPercent;
            public String PlannedPercent;
            public String DeliveryDate;
            public String Delta;
            public DateTime PurchO;
        }

        public class Rel_Path
        {
            public String RelPath = "c:/webs/JustMVCTest/JustMVCTest/pics/report.jpg";
        }

        public ActionResult Index()
        {
            DropDownList drop_model = new DropDownList();

            drop_model.CC_Type = new List<SelectListItem>();
            drop_model.IO_Type = new List<SelectListItem>();
            drop_model.Req_Type = new List<SelectListItem>();

                drop_model.Req_Type = db.Papi_Users_tbl.OrderBy(x => x.Requisitio).Select(x => new SelectListItem
                {
                    Value = x.Requisitio,
                    Text = x.Requisitio
                }).ToList();

                drop_model.CC_Type = db.POs.ToList().Where(x => x.Cost_Cent != null).Where(x => x.Cost_Cent != "").OrderBy(x => x.Cost_Cent).GroupBy(o => new { Cost_Cent = o.Cost_Cent }).Select(x => new SelectListItem
                {
                    Value = x.Key.Cost_Cent,
                    Text = x.Key.Cost_Cent
                }).ToList();

                drop_model.IO_Type = db.POs.ToList().Where(x => x.Int_Order != null).Where(x => x.Int_Order != "").OrderBy(x => x.Int_Order).GroupBy(o => new { Int_Order = o.Int_Order }).Select(x => new SelectListItem
                {
                    Value = x.Key.Int_Order,
                    Text = x.Key.Int_Order
                }).ToList();
                drop_model.VN_Type = db.POs.ToList().Where(x => x.VendorName != null).Where(x => x.VendorName != "").OrderBy(x => x.VendorName).GroupBy(o => new { Vendor_Name = o.VendorName }).Select(x => new SelectListItem
                {
                    Value = x.Key.Vendor_Name,
                    Text = x.Key.Vendor_Name
                }).ToList();
                drop_model.First_Time = true;
            return View(drop_model);
        }

        /*
        public ActionResult PrintPOs(string[] inputs)
        {
            return View();
        }
         */

        /*
        public ActionResult PrintPOs(string Req_List, string CC_List, string ReportType, string Per, int? POItem, int? PONumber, string DomainUsername, string VendorName, int? POPercentage)
        {
            return View();
        }
         */

        public ActionResult PrintPOs(string SubmitButton, string Req_List, string CC_List, string CC_AMT_List, 
            List<String> CC_Mul_Amt_Listbox, List<String> IO_Mul_Amt_Listbox, List<String> CC_Mul_DD_Listbox, List<String> IO_Mul_DD_Listbox,
            string[] CC_Mul_List, string[] IO_Mul_List, string IO_List, string VN_List, string ReportType, string Per,
            string FromTopDelaying, string ToTopDelaying,
            string CC_AMT_His_List, string IO_AMT_His_List, string FromCCAmtHis = null, string ToCCAmtHis = null,
            string FromIOAmtHis = null, string ToIOAmtHis = null,
            int POItem = 999, long PONumber = 999, string DomainUsername = "undefined", string VendorName = "undefined", int POPercentage = 999
            )
        {
            DropDownList drop_model = new DropDownList();

            drop_model.CC_Type = new List<SelectListItem>();
            drop_model.IO_Type = new List<SelectListItem>();
            drop_model.Req_Type = new List<SelectListItem>();
            String Rel_Path_Report = "http://localhost:8888";
            String Params = "&rc:Parameters=false";

           /* drop_model.Req_Type = db.Papi_Users_tbl.Select(x => new SelectListItem
            {
                Value = x.Requisitio,
                Text = x.Requisitio
            }).ToList();

            drop_model.CC_Type = db.POs.ToList().Where(x => x.Cost_Cent != null).Where(x => x.Cost_Cent != "").GroupBy(o => new { Cost_Cent = o.Cost_Cent }).Select(x => new SelectListItem
            {
                Value = x.Key.Cost_Cent,
                Text = x.Key.Cost_Cent
            }).ToList();

            drop_model.IO_Type = db.POs.ToList().Where(x => x.Int_Order != null).Where(x => x.Int_Order != "").GroupBy(o => new { Int_Order = o.Int_Order }).Select(x => new SelectListItem
            {
                Value = x.Key.Int_Order,
                Text = x.Key.Int_Order
            }).ToList();
            drop_model.VN_Type = db.POs.ToList().Where(x => x.VendorName != null).Where(x => x.VendorName != "").GroupBy(o => new { Vendor_Name = o.VendorName }).Select(x => new SelectListItem
            {
                Value = x.Key.Vendor_Name,
                Text = x.Key.Vendor_Name
            }).ToList();
            drop_model.First_Time = false; */
            
            String POItemstr;
            if (POItem != 999)
                {
                    POItemstr = POItem.ToString();
                }
            else 
            {
                POItemstr = "ALL";
            }
            String HistoryView;
            if (POItem != 999) HistoryView = "PO_History";
            else HistoryView = "PO_History_All_Items";
            if (ReportType == "Sum") {
                        if (Per == "sum_user")
                        {
                            if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fSUM_USER_REP&rs:Command=Render" + Params);
                            return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fSUM_USER_GPH&rs:Command=Render" + Params);
                        }
                        if (Per == "sum_cc")
                        {
                            if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fSUM_CC_REP&rs:Command=Render" + Params);
                            return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fSUM_CC_GPH&rs:Command=Render" + Params);
                        }
                        if (Per == "sum_io")
                        {
                            if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fSUM_IO_REP&rs:Command=Render" + Params);
                            return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fSUM_IO_GPH&rs:Command=Render" + Params);
                        }
                        if (Per == "sum_vn")
                        {
                            if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fSUM_VENDOR_REP&rs:Command=Render" + Params);
                            return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fSUM_VENDOR_GPH&rs:Command=Render" + Params);
                        }
            }
            switch (ReportType) {
                case "user":
                    /*var res_user = Requestio(Req_List);
                    if (res_user.Requestio_Res.Count == 0) return Content("No PO's Have Found");
                    return this.ViewPdf("Worker: " + Req_List, "Requestio", res_user);*/
                    return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fUSER_REP&rs:Command=Render" + "&req=" + Req_List + Params);
                case "cc":                    
                    /*var res_cc = CostCenter(CC_List);
                    if (res_cc.Requestio_Res.Count == 0) return Content("No PO's Have Found");
                    return this.ViewPdf("Cost Center: " + CC_List, "Requestio", res_cc);*/
                    return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fCC_REP&rs:Command=Render" + "&cc=" + CC_List + Params);
                case "io":
                    /*var res_io = InternalOrder(IO_List);
                    if (res_io.Requestio_Res.Count == 0) return Content("No PO's Have Found");
                    return this.ViewPdf("Internal Order: " + IO_List, "Requestio", res_io);*/
                    return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fIO_REP&rs:Command=Render" + "&io=" + IO_List + Params);
                case "vendor":
                    /*var res_vn = Vendor(VN_List);
                    if (res_vn.Vendor_Res.Count == 0) return Content("No PO's Have Found");
                    return this.ViewPdf("Vendor: " + VN_List, "Vendor", res_vn);*/
                    VN_List = VN_List.Replace('&', '^');
                    return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fVENDOR_REP&rs:Command=Render" + "&ven=" + VN_List + Params);
                case "delay":
                    /*var res_delay = TopDelaying();
                    if (res_delay.Count == 0) return Content("No PO's Have Found");
                    return this.ViewPdf("Top Delaying Workers Report", "TopDelaying", res_delay);*/
                    DateTime FromDelayDate = Convert.ToDateTime(FromTopDelaying);
                    FromTopDelaying = FromDelayDate.ToString("MM/dd/yyyy");
                    DateTime ToDelayDate = Convert.ToDateTime(ToTopDelaying);
                    ToTopDelaying = ToDelayDate.ToString("MM/dd/yyyy");
                    if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fTOP_DELAYING_REP&rs:Command=Render" + "&from=" + FromTopDelaying + "&to=" + ToTopDelaying + Params);
                    else return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fTOP_DELAYING_GPH&rs:Command=Render" + "&from=" + FromTopDelaying + "&to=" + ToTopDelaying + Params);
                case "history":
                    /*var res_his = PO_History(PONumber, POItem);
                    if (res_his.PO_History_Res.Count == 0) return Content("No PO's Have Found");
                    return this.ViewPdf("History of PO With PO Number: " + PONumber + ", Item: " + POItemstr, HistoryView, res_his);*/
                    return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fPO_HIS_REP&rs:Command=Render" + "&po_num=" + PONumber + "&item=" + POItem + Params);
                case "rate":
                    var res_rate = Rate_Rep(POPercentage);
                    if (res_rate.Count == 0) return Content("No PO's Have Found");
                    return this.ViewPdf("Percentage Report", "Rate", res_rate);
                case "cc_amt_his":
                    db.Database.ExecuteSqlCommand("DELETE FROM MonthYears");
                    DateTime FromCCAmtHisDate = Convert.ToDateTime(FromCCAmtHis);
                    DateTime ToCCAmtHisDate = Convert.ToDateTime(ToCCAmtHis);
                    int DiffMonthesCC = ((ToCCAmtHisDate.Year - FromCCAmtHisDate.Year) * 12) + ToCCAmtHisDate.Month - FromCCAmtHisDate.Month;
                    DiffMonthesCC++;
                    int CurrMonthCC = FromCCAmtHisDate.Month;
                    int CurrYearCC = FromCCAmtHisDate.Year;
                    DateTime CurrLastInMonthCC;
                    string InsertDateCC;
                    var restempcc = db.MonthYear_tbl.Select(x => x.MthYear);
                    for (; DiffMonthesCC > 0; DiffMonthesCC--)
                      {
                          if (CurrMonthCC == 13)
                          {
                              CurrMonthCC = 1;
                              CurrYearCC++;
                          }
                        CurrLastInMonthCC = Convert.ToDateTime("1/" + (CurrMonthCC.ToString()) + "/" + (CurrYearCC.ToString())); //now it's the first date in curr month
                        CurrLastInMonthCC = CurrLastInMonthCC.AddDays(1 - CurrLastInMonthCC.Day).AddMonths(1).AddSeconds(-1); //and now it's the last
                        String CurrLastInMonthDateCC = CurrLastInMonthCC.ToString("yyyy-MM-dd");
                        InsertDateCC = @"INSERT INTO MonthYears(MthYear) VALUES('" + CurrLastInMonthDateCC + "')";
                         db.Database.ExecuteSqlCommand(InsertDateCC);
                         CurrMonthCC++;
                      }
                    if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fCC_AMT_HIS_REP&rs:Command=Render" + "&cc=" + CC_AMT_His_List + Params);
                    else return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fCC_AMT_HIS_GPH&rs:Command=Render" + "&cc=" + CC_AMT_His_List + Params);
                case "io_amt_his":
                    db.Database.ExecuteSqlCommand("DELETE FROM MonthYears");
                    DateTime FromIOAmtHisDate = Convert.ToDateTime(FromIOAmtHis);
                    DateTime ToIOAmtHisDate = Convert.ToDateTime(ToIOAmtHis);
                    int DiffMonthesIO = ((ToIOAmtHisDate.Year - FromIOAmtHisDate.Year) * 12) + ToIOAmtHisDate.Month - FromIOAmtHisDate.Month;
                    DiffMonthesIO++;
                    int CurrMonthIO = FromIOAmtHisDate.Month;
                    int CurrYearIO = FromIOAmtHisDate.Year;
                    DateTime CurrLastInMonthIO;
                    string InsertDateIO;
                    var restempio = db.MonthYear_tbl.Select(x => x.MthYear);
                    for (; DiffMonthesIO > 0; DiffMonthesIO--)
                    {
                        if (CurrMonthIO == 13)
                        {
                            CurrMonthIO = 1;
                            CurrYearIO++;
                        }
                        CurrLastInMonthIO = Convert.ToDateTime("1/" + (CurrMonthIO.ToString()) + "/" + (CurrYearIO.ToString())); //now it's the first date in curr month
                        CurrLastInMonthIO = CurrLastInMonthIO.AddDays(1 - CurrLastInMonthIO.Day).AddMonths(1).AddSeconds(-1); //and now it's the last
                        String CurrLastInMonthDateIO = CurrLastInMonthIO.ToString("yyyy-MM-dd");
                        InsertDateIO = @"INSERT INTO MonthYears(MthYear) VALUES('" + CurrLastInMonthDateIO + "')";
                        db.Database.ExecuteSqlCommand(InsertDateIO);
                        CurrMonthIO++;
                    }
                    if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fIO_AMT_HIS_REP&rs:Command=Render" + "&io=" + IO_AMT_His_List + Params);
                    else return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fIO_AMT_HIS_GPH&rs:Command=Render" + "&io=" + IO_AMT_His_List + Params);
               
                case "cc_amt":
                    if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fCC_AMT_REP&rs:Command=Render" + "&cc=" + CC_AMT_List + Params);
                    else return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fCC_AMT_GPH&rs:Command=Render" + "&cc=" + CC_AMT_List + Params);
                case "mul_cc_amt":
                    /*For Using CHECKBOXES*/   
                /* String CC_Address = "";
                    foreach (string cc in CC_Mul_List)
                        {
                            if (cc != "false" && cc != "true")
                            {
                                CC_Address += "&cc=" + cc;
                            }
                        } */
                   String Mul_CC_Address_Amt = ""; 
                   foreach (string cc in CC_Mul_Amt_Listbox)
                    {
                        Mul_CC_Address_Amt += "&cc=" + cc; 
                    }
                   if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fMUL_CC_REP&rs:Command=Render" + Mul_CC_Address_Amt + Params);
                   return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fMUL_CC_GPH&rs:Command=Render" + Mul_CC_Address_Amt + Params);
                case "mul_io_amt":
                    /*String IO_Address = "";
                    foreach (string io in IO_Mul_List)
                    {
                        if (io != "false" && io != "true")
                        {
                            IO_Address += "&io=" + io;
                        }
                    } */
                    String Mul_IO_Address_Amt = ""; 
                   foreach (string io in IO_Mul_Amt_Listbox)
                    {
                        Mul_IO_Address_Amt += "&io=" + io; 
                    }
                   if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fMUL_IO_REP&rs:Command=Render" + Mul_IO_Address_Amt + Params);
                   return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fMUL_IO_GPH&rs:Command=Render" + Mul_IO_Address_Amt + Params);
                case "mul_cc_dd":
                   String Mul_CC_Address_DD = "";
                   foreach (string cc in CC_Mul_DD_Listbox)
                    {
                        Mul_CC_Address_DD += "&cc=" + cc;
                    }
                   if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fMUL_CC_DD_REP&rs:Command=Render" + Mul_CC_Address_DD + Params);
                   return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fMUL_CC_DD_GPH&rs:Command=Render" + Mul_CC_Address_DD + Params);
                case "mul_io_dd":
                   String Mul_IO_Address_DD = "";
                    foreach (string io in IO_Mul_DD_Listbox)
                    {
                        Mul_IO_Address_DD += "&io=" + io;
                    }
                    if (SubmitButton == "View Report") return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fMUL_IO_DD_REP&rs:Command=Render" + Mul_IO_Address_DD + Params);
                    return this.Redirect(Rel_Path_Report + "/ReportServer/Pages/ReportViewer.aspx?%2fReportProject%2fMUL_IO_DD_GPH&rs:Command=Render" + Mul_IO_Address_DD + Params);

                default: //never happens
                    return View("Index");
            }
                
        }

        private List<Rate_Res> Rate_Rep(int POPercentage)
        {
            var res = db.POs.Where(x => x.latest == 1).Where(x => x.Percentage <= POPercentage)
            .OrderBy(o => o.Percentage)
            .Select(o => new Rate { CostCenter = o.Cost_Cent, PONumber = o.PONumber, Item = o.Item, ActualPercent= o.Percentage, PurchO = o.Purchase_O, DeliveryDate = o.DeliveryDate})
            .ToList();

            List<Rate_Res> myPO_Report2List = new List<Rate_Res>();

            foreach (Rate pO in (List<Rate>)res)
            {
                Rate_Res pO_Rep7 = new Rate_Res();
                pO_Rep7.CostCenter = pO.CostCenter;
                pO_Rep7.PONumber = pO.PONumber;
                pO_Rep7.Item = pO.Item;
                if (DateTime.Now >= pO.DeliveryDate)
                {
                    pO_Rep7.PlannedPercent = "100%";
                    int Delta = pO.PlannedPercent - pO.ActualPercent;
                    pO_Rep7.Delta = Delta.ToString();
                    pO_Rep7.Delta += "%";
                }
                else
                {
                    int DaysPass = (int)(DateTime.Now - pO.PurchO).TotalDays;
                    int TotalDays = (int)(pO.DeliveryDate - pO.PurchO).TotalDays;
                    int PlannedPercentInt = 100 * DaysPass / TotalDays;
                    //PlannedPercentInt = Math.Round(PlannedPercentInt, 2);
                    int DeltaTmp = pO.ActualPercent - PlannedPercentInt;
                    pO_Rep7.Delta = DeltaTmp.ToString();
                    pO_Rep7.Delta += "%";
                    pO_Rep7.PlannedPercent = PlannedPercentInt.ToString();
                    pO_Rep7.PlannedPercent += "%";
                }
                pO_Rep7.ActualPercent = pO.ActualPercent.ToString();
                pO_Rep7.ActualPercent += "%";
                pO_Rep7.DeliveryDate = pO.DeliveryDate.ToString("dd/MM/yyyy"); ;
                myPO_Report2List.Add(pO_Rep7);
            }
            return (List<Rate_Res>)myPO_Report2List;
        }
        
        
        private Main_PO_History PO_History(long PONumber, int POItem)
        {
            
            var res = db.POs.Where(x => x.PONumber == PONumber).Where(x => x.Item == POItem)
            .OrderBy(o => o.DeliveryDate).ThenByDescending(o => o.Update_Num)
            .Select(o => new PO_History_Rep { Update_Num = o.Update_Num, Status = o.Status, DeliveryDate = o.DeliveryDate, DeliveryDate_Req = o.DeliveryDate_Req, Percentage = o.Percentage })
            .ToList();
        
            var res_vendors = db.POs.Where(x => x.PONumber == PONumber).Where(x => x.Item == POItem).Select(o => o.VendorName);
            String Vendor_Name_tmp = null;
            if (res_vendors.Count() > 0) 
            {
                Vendor_Name_tmp = res_vendors.First();
            }

            var res_decr = db.POs.Where(x => x.PONumber == PONumber).Where(x => x.Item == POItem).Select(o => o.Descr);
            String Descr_tmp = null;
            if (res_decr.Count() > 0)
            {
                Descr_tmp = res_decr.First();
            }

            //ViewData["Item"] = "Exists";

            if (POItem == 999) //if user didn't insert item, returns all po's with given ponumber
            {
                //ViewData["Item"] = "NotExists";
                res = db.POs.Where(x => x.PONumber == PONumber)
                .OrderBy(o => o.Item).ThenByDescending(o => o.Update_Num)
                .Select(o => new PO_History_Rep { Item = o.Item, Update_Num = o.Update_Num, Status = o.Status, DeliveryDate = o.DeliveryDate, DeliveryDate_Req = o.DeliveryDate_Req, Percentage = o.Percentage })
                .ToList();
            }


            List<PO_History_Res> myPO_Report2List = new List<PO_History_Res>();

            foreach (PO_History_Rep pO in (List<PO_History_Rep>)res)
            {
                PO_History_Res pO_Rep6 = new PO_History_Res();
                pO_Rep6.Item = pO.Item;
                pO_Rep6.Update_Num = pO.Update_Num;
                switch (pO.Status)
                {
                    case 0:
                        pO_Rep6.Status = "Open";
                        break;
                    case 1:
                        pO_Rep6.Status = "Pending Close";
                        break;
                    case 2:
                        pO_Rep6.Status = "Pending Delivery Date";
                        break;
                    case 99:
                        pO_Rep6.Status = "Closed";
                        break;
                }
                pO_Rep6.DeliveryDate = pO.DeliveryDate.ToString("dd/MM/yyyy");
                pO_Rep6.DeliveryDate_Req = pO.DeliveryDate_Req.ToString("dd/MM/yyyy");
                pO_Rep6.Percentage = pO.Percentage.ToString();
                pO_Rep6.Percentage += "%";
                myPO_Report2List.Add(pO_Rep6);
            }
            PO_History_HeadLine HL = new PO_History_HeadLine();
            HL.Vendor_Name = Vendor_Name_tmp;
            HL.Descr = Descr_tmp;
            Main_PO_History Main = new Main_PO_History();
            Main.HeadLine = HL;
            Main.PO_History_Res = myPO_Report2List;
            return Main;
        }

        private List<TopDelaying_Rep> TopDelaying()
        {
            var pos1 = from poes in db.POs
                       where (poes.Status == 0 || poes.Status == 2)
                      select poes;
            var pos2 = from poes in db.POs
                       where (poes.Status == 0 || poes.Status == 2)
                       select poes;
            var pos3 = from poes1 in db.POs
                       join poes2 in pos1 on new { poes1.PONumber, poes1.Item, x = poes1.Update_Num, y = poes1.Status } equals new { poes2.PONumber, poes2.Item, x = poes2.Update_Num - 1, y = poes2.Status - 2}
                       select new { poes1.PONumber, poes1.Item, poes1.Requisitioner, poes1.Update_Num };

            var tmp = from users in db.Papi_Users_tbl
                      join poes in pos3 on users.Requisitio equals poes.Requisitioner
                      select new { Name = users.domain_username, Email = users.email, PONumber = poes.PONumber, Item = poes.Item, Update_Num = poes.Update_Num };

            var res = tmp.GroupBy(o => new { Name = o.Name, Email = o.Email })
            .Select(o => new TopDelaying_Rep { Name = o.Key.Name, Email = o.Key.Email, Count = o.Count() })
            .OrderByDescending(o => o.Count)
            .ToList();

            List<TopDelaying_Rep> myPO_Report2List = new List<TopDelaying_Rep>();

            foreach (TopDelaying_Rep pO in (List<TopDelaying_Rep>)res)
            {
                TopDelaying_Rep pO_Rep5 = new TopDelaying_Rep();
                pO_Rep5.Name = pO.Name;
                pO_Rep5.Email = pO.Email;
                pO_Rep5.Count = pO.Count;
                myPO_Report2List.Add(pO_Rep5);
            }
            return (List<TopDelaying_Rep>)myPO_Report2List;
        }

        
       private Main_Vendor Vendor(String VendorName)
        {
            var res = db.POs.Where(x => x.latest == 1).Where(x => x.VendorName == VendorName)
            .OrderBy(o => o.DeliveryDate)
            .Select(o => new Vendor_Rep { PONumber = o.PONumber, Item = o.Item, Descr = o.Descr, DeliveryDate = o.DeliveryDate, Status = o.Status})
            .ToList();

            List<Vendor_Res> myPO_Report2List = new List<Vendor_Res>();

            var Vendor_Count_PO = 0;
            foreach (Vendor_Rep pO in (List<Vendor_Rep>)res)
            {
                Vendor_Count_PO++;
                Vendor_Res pO_Rep1 = new Vendor_Res();
                pO_Rep1.PONumber = pO.PONumber;
                pO_Rep1.Item = pO.Item;
                pO_Rep1.Descr = pO.Descr;
                pO_Rep1.DeliveryDate = pO.DeliveryDate.ToString("dd/MM/yyyy");
                switch (pO.Status)
                {
                    case 0:
                        pO_Rep1.Status = "Open";
                        break;
                    case 1:
                        pO_Rep1.Status = "Pending Close";
                        break;
                    case 2:
                        pO_Rep1.Status = "Pending Delivery Date";
                        break;
                    case 99:
                        pO_Rep1.Status = "Closed";
                        break;
                }
                
                myPO_Report2List.Add(pO_Rep1);
            }
            HeadLine HL = new HeadLine();
            HL.CountPos = Vendor_Count_PO;
            Main_Vendor Main = new Main_Vendor();
            Main.HeadLine = HL;
            Main.Vendor_Res = myPO_Report2List;
            return Main;
        }

        private Main_Requestio_Rep Requestio(string Requestio)
        {
            var res = db.POs.Where(x => x.latest == 1).Where(x=> x.Requisitioner == Requestio).Where(x => DateTime.Now > x.DeliveryDate).Where(x=> x.Status != 99)
            .Select(o => new Requestio_Rep { PONumber = o.PONumber, Item = o.Item, Vendor_Name = o.VendorName, Descr = o.Descr, Open_Amt_L = o.Open_Amt_L, DeliveryDate = o.DeliveryDate })
            .OrderBy(o => o.DeliveryDate)
            .ToList();

            List<Requestio_Res> myPO_Report2List = new List<Requestio_Res>();

            var User_Count_PO = 0;
            foreach (Requestio_Rep pO in (List<Requestio_Rep>)res)
            {
                User_Count_PO++;
                Requestio_Res pO_Rep8 = new Requestio_Res();
                pO_Rep8.PONumber = pO.PONumber;
                pO_Rep8.Item = pO.Item;
                pO_Rep8.Vendor_Name = pO.Vendor_Name;
                pO_Rep8.Descr = pO.Descr;
                pO_Rep8.Open_Amt_L = Math.Round(pO.Open_Amt_L, 2);
                pO_Rep8.DeliveryDate = pO.DeliveryDate.ToString("dd/MM/yyyy");
                myPO_Report2List.Add(pO_Rep8);
            }

            HeadLine HL = new HeadLine();
            HL.CountPos = User_Count_PO;
            Main_Requestio_Rep Main = new Main_Requestio_Rep();
            Main.HeadLine = HL;
            Main.Requestio_Res = myPO_Report2List;
            return Main;
        }


        private Main_Requestio_Rep InternalOrder(string IO)
        {
            var res = db.POs.Where(x => x.latest == 1).Where(x => x.Int_Order == IO).Where(x => DateTime.Now > x.DeliveryDate).Where(x => x.Status != 99)
            .Select(o => new Requestio_Rep { PONumber = o.PONumber, Item = o.Item, Vendor_Name = o.VendorName, Descr = o.Descr, Open_Amt_L = o.Open_Amt_L, DeliveryDate = o.DeliveryDate })
            .OrderBy(o => o.DeliveryDate)
            .ToList();

            List<Requestio_Res> myPO_Report2List = new List<Requestio_Res>();

            var User_Count_PO = 0;
            foreach (Requestio_Rep pO in (List<Requestio_Rep>)res)
            {
                User_Count_PO++;
                Requestio_Res pO_Rep8 = new Requestio_Res();
                pO_Rep8.PONumber = pO.PONumber;
                pO_Rep8.Item = pO.Item;
                pO_Rep8.Vendor_Name = pO.Vendor_Name;
                pO_Rep8.Descr = pO.Descr;
                pO_Rep8.Open_Amt_L = Math.Round(pO.Open_Amt_L, 2);
                pO_Rep8.DeliveryDate = pO.DeliveryDate.ToString("dd/MM/yyyy");
                myPO_Report2List.Add(pO_Rep8);
            }

            HeadLine HL = new HeadLine();
            HL.CountPos = User_Count_PO;
            Main_Requestio_Rep Main = new Main_Requestio_Rep();
            Main.HeadLine = HL;
            Main.Requestio_Res = myPO_Report2List;
            return Main;
        }


        private Main_Requestio_Rep CostCenter(string CC)
        {
            var res = db.POs.Where(x => x.latest == 1).Where(x => x.Cost_Cent == CC).Where(x => DateTime.Now > x.DeliveryDate).Where(x => x.Status != 99)
            .Select(o => new Requestio_Rep { PONumber = o.PONumber, Item = o.Item, Vendor_Name = o.VendorName, Descr = o.Descr, Open_Amt_L = o.Open_Amt_L, DeliveryDate = o.DeliveryDate })
            .OrderBy(o => o.DeliveryDate)
            .ToList();

            List<Requestio_Res> myPO_Report2List = new List<Requestio_Res>();

            var User_Count_PO = 0;
            foreach (Requestio_Rep pO in (List<Requestio_Rep>)res)
            {
                User_Count_PO++;
                Requestio_Res pO_Rep8 = new Requestio_Res();
                pO_Rep8.PONumber = pO.PONumber;
                pO_Rep8.Item = pO.Item;
                pO_Rep8.Vendor_Name = pO.Vendor_Name;
                pO_Rep8.Descr = pO.Descr;
                pO_Rep8.Open_Amt_L = Math.Round(pO.Open_Amt_L, 2);
                pO_Rep8.DeliveryDate = pO.DeliveryDate.ToString("dd/MM/yyyy");
                myPO_Report2List.Add(pO_Rep8);
            }

            HeadLine HL = new HeadLine();
            HL.CountPos = User_Count_PO;
            Main_Requestio_Rep Main = new Main_Requestio_Rep();
            Main.HeadLine = HL;
            Main.Requestio_Res = myPO_Report2List;
            return Main;
        }

        private Main_Vendor_Sum Vendor_Sum_Rep()
        {
            var pos = from poes in db.POs
                      where (poes.latest == 1 && DateTime.Now > poes.DeliveryDate && poes.Status != 99)
                      select poes;
            var res = pos.GroupBy(o => new { VendorName = o.VendorName})
            .Select(o => new Vendor_Sum { Open_Amt_L_Sum = o.Sum(s=> s.Open_Amt_L), Count = o.Count(), VendorName = o.Key.VendorName})
            .OrderByDescending(o => o.Open_Amt_L_Sum)
            .ToList();

            List<Vendor_Sum> myPO_Report2List = new List<Vendor_Sum>();
            var Vendor_Count_PO = 0;
            foreach (Vendor_Sum pO in (List<Vendor_Sum>)res)
            {
                Vendor_Count_PO++;
                Vendor_Sum pO_Rep8 = new Vendor_Sum();
                pO_Rep8.Open_Amt_L_Sum = Math.Round(pO.Open_Amt_L_Sum, 2);
                pO_Rep8.Count = pO.Count;
                pO_Rep8.VendorName = pO.VendorName;
                myPO_Report2List.Add(pO_Rep8);
            }
            HeadLine HL = new HeadLine();
            HL.CountPos = Vendor_Count_PO;
            Main_Vendor_Sum Main = new Main_Vendor_Sum();
            Main.HeadLine = HL;
            Main.Vendor_Sum = myPO_Report2List;
            return Main;

        }


        private List<Requestio_Sum> Requestio_Sum_Rep()
        {
            var pos = from poes in db.POs
                      where (poes.latest == 1 && DateTime.Now > poes.DeliveryDate && poes.Status != 99)
                      select poes;
            var res = pos.GroupBy(o => new { Requestioner = o.Requisitioner })
            .Select(o => new Requestio_Sum { Requestio = o.Key.Requestioner, Count = o.Count() })
            .OrderByDescending(o => o.Count)
            .ToList();

            List<Requestio_Sum> myPO_Report2List = new List<Requestio_Sum>();

            foreach (Requestio_Sum pO in (List<Requestio_Sum>)res)
            {
                Requestio_Sum pO_Rep8 = new Requestio_Sum();
                pO_Rep8.Requestio = pO.Requestio;
                pO_Rep8.Count = pO.Count;
                myPO_Report2List.Add(pO_Rep8);
            }
            return (List<Requestio_Sum>)myPO_Report2List;
        }

        private List<CC_Sum> CC_Sum_Rep()
        {
            var pos = from poes in db.POs
                      where (poes.latest == 1 && DateTime.Now > poes.DeliveryDate && poes.Status != 99 && poes.Cost_Cent != "")
                      select poes;
            var res = pos.GroupBy(o => new { CC = o.Cost_Cent })
            .Select(o => new CC_Sum { CC = o.Key.CC, Count = o.Count() })
            .OrderByDescending(o => o.Count)
            .ToList();

            List<CC_Sum> myPO_Report2List = new List<CC_Sum>();

            foreach (CC_Sum pO in (List<CC_Sum>)res)
            {
                CC_Sum pO_Rep8 = new CC_Sum();
                pO_Rep8.CC = pO.CC;
                pO_Rep8.Count = pO.Count;
                myPO_Report2List.Add(pO_Rep8);
            }
            return (List<CC_Sum>)myPO_Report2List;
        }

        private List<IO_Sum> IO_Sum_Rep()
        {
            var pos = from poes in db.POs
                      where (poes.latest == 1 && DateTime.Now > poes.DeliveryDate && poes.Status != 99 && poes.Int_Order != "")
                      select poes;
            var res = pos.GroupBy(o => new { IO = o.Int_Order })
            .Select(o => new IO_Sum { IO = o.Key.IO, Count = o.Count() })
            .OrderByDescending(o => o.Count)
            .ToList();

            List<IO_Sum> myPO_Report2List = new List<IO_Sum>();

            foreach (IO_Sum pO in (List<IO_Sum>)res)
            {
                IO_Sum pO_Rep8 = new IO_Sum();
                pO_Rep8.IO = pO.IO;
                pO_Rep8.Count = pO.Count;
                myPO_Report2List.Add(pO_Rep8);
            }
            return (List<IO_Sum>)myPO_Report2List;
        }
       
    }
}